package data_access;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import modelo.Informe;
import modelo.Observacion;

public class InformesManager
{
	public static Collection<Informe> getAll()
	{
		Connection c = ConnectionFactory.getConnection();
		try
		{
			PreparedStatement ps = c
					.prepareStatement("SELECT nro, tecnica, matricula, idDiagnostico, idFormulario, fecha FROM Informes");
			ResultSet rs = ps.executeQuery();

			Collection<Informe> ci = new ArrayList<Informe>();
			while (rs.next())
			{
				Informe i = new Informe();

				int nro = rs.getInt(1);
				i.setNro(nro);
				String tecnica = rs.getString(2);
				i.setTecnica(tecnica);

				int idMedico = rs.getInt(3);
				i.setMedico(MedicosManager.getById(idMedico));

				int idDiagnostico = rs.getInt(4);
				i.setDiagnostico(DiagnosticosManager.getById(idDiagnostico));

				int idFormulario = rs.getInt(5);
				i.setFormulario(FormulariosManager.getById(idFormulario));

				Date fecha = rs.getDate(6);
				i.setFecha(fecha);

				i.addSintomas(ObservacionesManager.getByInforme(nro, c));

				ci.add(i);
			}
			return ci;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(c);
		}

		return null;
	}

	public static void Save(Informe informe)
	{
		int idDiagnostico = DiagnosticosManager.Save(informe.getDiagnostico());
		Collection<Integer> ids = ObservacionesManager.Save(informe.getObservaciones());

		Connection c = ConnectionFactory.getConnection();
		try
		{
			String sql = "INSERT INTO Informes (tecnica, idMedico, idDiagnostico, idFormulario, fecha) VALUES (?, ?, ?, ?, ?)";
			PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, informe.getTecnica());
			ps.setInt(2, informe.getMedico().getMatricula());
			ps.setInt(3, idDiagnostico);
			ps.setInt(4, informe.getFormulario().getNro());
			ps.setDate(5, informe.getFecha());
			ps.executeUpdate();
			
			FormulariosManager.UpdateEstado(informe.getFormulario());

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next())
			{
				int generatedId = rs.getInt(1);
				for (int idObservacion : ids)
				{
					insertInformeObservacion(generatedId, idObservacion);
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(c);
		}
	}

	private static void insertInformeObservacion(int idInforme, int idObservacion)
	{
		Connection c = ConnectionFactory.getConnection();
		try
		{
			PreparedStatement ps = c
					.prepareStatement("INSERT INTO Informes_Observaciones (idInforme, idObservacion) VALUES (?,?)");
			ps.setInt(1, idInforme);
			ps.setInt(2, idObservacion);
			ps.execute();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	public static Collection<Informe> getByObservaciones(Collection<Observacion> obs)
	{
		Collection<Informe> ci = new ArrayList<Informe>();
		if(obs != null && !obs.isEmpty())
			{
			for (Observacion o : obs)
			{
				ci.addAll(getByObservacion(o.getId()));
			}
		}

		Collection<Informe> rv = new ArrayList<Informe>();
		for (Informe i : ci)
		{
			if (i.getObservaciones().containsAll(obs))
			{
				rv.add(i);
			}
		}
		return ci;
	}

	private static Collection<Informe> getByObservacion(int id)
	{
		Connection c = ConnectionFactory.getConnection();
		try
		{
			PreparedStatement ps = c
					.prepareStatement("SELECT idInforme FROM Informes_Observaciones"
							+ " WHERE idObservacion = ?");
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();

			Collection<Informe> ci = new ArrayList<Informe>();
			while (rs.next())
			{
				ci.add(getById(rs.getInt(1)));
			}

			return ci;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(c);
		}

		return null;
	}

	public static Informe getById(int nro)
	{
		Connection c = ConnectionFactory.getConnection();
		try
		{
			PreparedStatement ps = c
					.prepareStatement("SELECT tecnica, idMedico, idDiagnostico, idFormulario, fecha"
							+ " FROM Informes" + " WHERE nro = ?");
			ps.setInt(1, nro);
			ResultSet rs = ps.executeQuery();

			Informe i = null;
			if (rs.next())
			{
				i = new Informe();

				String tecnica = rs.getString(1);
				i.setTecnica(tecnica);

				int idMedico = rs.getInt(2);
				i.setMedico(MedicosManager.getById(idMedico));

				int idDiagnostico = rs.getInt(3);
				i.setDiagnostico(DiagnosticosManager.getById(idDiagnostico));

				int idFormulario = rs.getInt(4);
				i.setFormulario(FormulariosManager.getById(idFormulario));

				Date fecha = rs.getDate(5);
				i.setFecha(fecha);

				i.addSintomas(ObservacionesManager.getByInforme(nro, c));
			}
			return i;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(c);
		}

		return null;
	}
}